package com.zd.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.zd.dao.BaseDao;
import com.zd.dao.NovelDao;
import com.zd.entity.Chapter;
import com.zd.entity.LBVipBook;
import com.zd.entity.Novel;
import com.zd.entity.NovelWriterType;
import com.zd.entity.User;


public class NovelDaoImpl implements NovelDao {
	
	
		

	@Override
	public List<String> novelType() {
		// TODO Auto-generated method stub
		//查询novelType
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "select * from novelType limit 0,14 ";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<String> typeList=new ArrayList<String>();
			while(rs.next()) {
				typeList.add(rs.getString("novelTypeName"));
			}
			return typeList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	
		
	}

	@Override
	public List<String[]> type_NovelName() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "select t2.novelTypeName,t1.novelName,t2.novelTypeId,t1.novelId ,t3.novelTypeName AS novelTypeName2 from novel t1,noveltype t2 ,noveltype t3\r\n"
					+ "WHERE t1.novelTypeId1=t2.novelTypeId\r\n"
					+ "AND t1.novelTypeId2=t3.novelTypeId and audit=1 \r\n"
					+ " ORDER BY RAND()";
//			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<String[]> typeList=new ArrayList<String[]>();
			String[] type= new String[18];
			String[] novelName=new String[18];
			String[] type2=new String[18];
			String[] typeId= new String[18];
			String[] novelNameId= new String[18];
			int num=0;
			while(rs.next()) {
				if(num==18) {
					break;
				}
				typeId[num]=Integer.toString(rs.getInt("novelTypeId"));
				novelNameId[num]=Integer.toString(rs.getInt("novelId"));
				type[num]=rs.getString("novelTypeName");
				novelName[num]=rs.getString("novelName");
				type2[num]=rs.getString("novelTypeName2");
//				System.out.println(type[num]+novelName[num]);
				num++;
			}
			typeList.add(type);
			
			typeList.add(novelName);
			typeList.add(typeId);
			typeList.add(novelNameId);
			typeList.add(type2);
//			System.out.println(typeList.get(0)[0]);
			return typeList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<Novel> novelHitsSelect() {//查小说点击的前几个
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT * FROM (\r\n"
					+ "  SELECT * FROM novel where audit=1 ORDER BY hits DESC LIMIT 0,10\r\n"
					+ ") AS temp_table  ORDER BY RAND() LIMIT 6;";
//			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<Novel> NovelList=new ArrayList<Novel>();
			while(rs.next()) {
				Novel novel=new Novel();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelIntroduction(rs.getString("novelIntroduction"));
				novel.setHits(rs.getInt("hits"));
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<NovelWriterType> novelVipSelect() {
		// TODO Auto-generated method stub
		
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT * from (SELECT t1.novelId,t1.hits,t1.novelName,t2.novelTypeName,t2.novelTypeId,t1.novelImgURL,t3.writerId,t3.writerName ,t4.novelTypeName as novelTypeName2\r\n"
					+ "FROM novel t1,noveltype t2,writer t3 ,noveltype t4\r\n"
					+ "WHERE t1.novelTypeId1=t2.novelTypeId \r\n"
					+ "AND t1.writerId=t3.writerId \r\n"
					+ "AND t1.novelTypeId2=t4.novelTypeId and audit=1 \r\n"
					+ "ORDER BY t1.releaseTime limit 0,60) t9\r\n"
					+ "ORDER BY RAND()\r\n"
					+ "limit 0,15";
//			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<NovelWriterType> NovelList=new ArrayList<NovelWriterType>();
			while(rs.next()) {
				NovelWriterType novel=new NovelWriterType();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				novel.setNovelTypeName2(rs.getString("novelTypeName2"));
				novel.setHits(rs.getInt("hits"));
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<NovelWriterType> SelectNewNovelList() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT t1.novelId,t1.novelName,t2.novelTypeName,t2.novelTypeId,t1.novelImgURL,t3.writerId,t3.writerName,t1.novelIntroduction ,t4.novelTypeName AS novelTypeName2,t1.hits\r\n"
					+ "FROM novel t1,noveltype t2,writer t3 ,noveltype t4\r\n"
					+ "WHERE t1.novelTypeId1=t2.novelTypeId \r\n"
					+ "AND t1.writerId=t3.writerId \r\n"
					+ "AND t1.novelTypeId2=t4.novelTypeId and audit=1 \r\n"
					+ "ORDER BY t1.releaseTime desc\r\n"
					+ "LIMIT 0,15";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<NovelWriterType> NovelList=new ArrayList<NovelWriterType>();
			while(rs.next()) {
				NovelWriterType novel=new NovelWriterType();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				novel.setNovelTypeName2(rs.getString("novelTypeName2"));
				String str=rs.getString("novelIntroduction");
				
				novel.setNovelIntroduction(str);
				novel.setHits(rs.getInt("hits"));
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<NovelWriterType> SelectNewNovelListMore() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT * FROM(\r\n"
					+ "SELECT t1.novelId,t1.novelName,t2.novelTypeName,t2.novelTypeId,t1.novelImgURL,t3.writerId,t3.writerName,t1.novelIntroduction,t1.releaseTime\r\n"
					+ "FROM novel t1,noveltype t2,writer t3 \r\n"
					+ "WHERE t1.novelTypeId1=t2.novelTypeId \r\n"
					+ "AND t1.writerId=t3.writerId and audit=1) t8 ORDER BY  releaseTime DESC LIMIT 0,6;";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<NovelWriterType> NovelList=new ArrayList<NovelWriterType>();
			while(rs.next()) {
				NovelWriterType novel=new NovelWriterType();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				
				String str=rs.getString("novelIntroduction");
//				String subStr = str.substring(0, 20);
				novel.setNovelIntroduction(str);
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<NovelWriterType> SelectNewNovelListMoreTen() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT * FROM(\r\n"
					+ "SELECT t1.novelId,t1.novelName,t2.novelTypeName,t2.novelTypeId,t1.novelImgURL,t3.writerId,t3.writerName,t1.novelIntroduction,t1.releaseTime\r\n"
					+ "FROM novel t1,noveltype t2,writer t3 \r\n"
					+ "WHERE t1.novelTypeId1=t2.novelTypeId \r\n"
					+ "AND t1.writerId=t3.writerId and audit=1) t8 ORDER BY  releaseTime DESC LIMIT 6,10;";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<NovelWriterType> NovelList=new ArrayList<NovelWriterType>();
			while(rs.next()) {
				NovelWriterType novel=new NovelWriterType();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				
				String str=rs.getString("novelIntroduction");
//				String subStr = str.substring(0, 20);
				novel.setNovelIntroduction(str);
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<NovelWriterType> SelectEndNovelListone() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT t1.novelId,t1.hits,t1.novelName,t3.novelTypeName,t3.novelTypeId,t1.novelImgURL,t2.writerId,t2.writerName,t1.novelIntroduction \r\n"
					+ "FROM novel t1,writer t2,noveltype t3  \r\n"
					+ "WHERE t1.writerId=t2.writerId \r\n"
					+ "and t1.novelTypeId1=t3.novelTypeId \r\n"
					+ "and t1.audit=1 ORDER BY RAND() limit 0,1;";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<NovelWriterType> NovelList=new ArrayList<NovelWriterType>();
			while(rs.next()) {
				NovelWriterType novel=new NovelWriterType();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				novel.setHits(rs.getInt("hits"));
				String str=rs.getString("novelIntroduction");
//				String subStr = str.substring(0, 30);
				novel.setNovelIntroduction(str);
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<NovelWriterType> SelectEndNovelListmore() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT t1.novelId,t1.hits,t1.novelName,t3.novelTypeName,t3.novelTypeId,t1.novelImgURL,t2.writerId,t2.writerName,t1.novelIntroduction \r\n"
					+ "FROM novel t1,writer t2,noveltype t3  \r\n"
					+ "WHERE t1.writerId=t2.writerId \r\n"
					+ "and t1.novelTypeId1=t3.novelTypeId \r\n"
					+ "and t1.audit=1 ORDER BY RAND() limit 0,6;";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<NovelWriterType> NovelList=new ArrayList<NovelWriterType>();
			while(rs.next()) {
				NovelWriterType novel=new NovelWriterType();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				novel.setHits(rs.getInt("hits"));
				String str=rs.getString("novelIntroduction");
//				String subStr = str.substring(0, 30);
				novel.setNovelIntroduction(str);
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}
	
	@Override
	public List<NovelWriterType> SelectEndNovelListten() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT t1.novelId,t1.hits,t1.novelName,t3.novelTypeName,t3.novelTypeId,t1.novelImgURL,t2.writerId,t2.writerName,t1.novelIntroduction \r\n"
					+ "FROM novel t1,writer t2,noveltype t3  \r\n"
					+ "WHERE t1.writerId=t2.writerId \r\n"
					+ "and t1.novelTypeId1=t3.novelTypeId \r\n"
					+ "and t1.audit=1 ORDER BY RAND() limit 0,10";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<NovelWriterType> NovelList=new ArrayList<NovelWriterType>();
			while(rs.next()) {
				NovelWriterType novel=new NovelWriterType();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				novel.setHits(rs.getInt("hits"));
				String str=rs.getString("novelIntroduction");
//				String subStr = str.substring(0, 30);
				novel.setNovelIntroduction(str);
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<NovelWriterType> SelectnovelN() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT t1.novelId,t1.novelName,t2.writerId,t2.writerName,t1.novelImgURL FROM novel t1,writer t2, chapter t3 WHERE t1.writerId=t2.writerId and t1.novelId=t3.novelId and t1.audit=1 and t3.audit=1 ORDER BY updateTime DESC LIMIT 0,3;";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<NovelWriterType> NovelList=new ArrayList<NovelWriterType>();
			while(rs.next()) {
				NovelWriterType novel=new NovelWriterType();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
//				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
//				novel.setNovelTypeName(rs.getString("novelTypeName"));
//				novel.setHits(rs.getInt("hits"));
//				String str=rs.getString("novelIntroduction");
//				String subStr = str.substring(0, 30);
//				novel.setNovelIntroduction(subStr);
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<Chapter> selectNoveChapter() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT t1.novelId,t1.novelName,t2.writerId,t2.writerName,t3.chapterId,t3.chapterName,t3.updateTime ,t4.novelTypeId,t4.novelTypeName\r\n"
					+ "FROM novel t1,writer t2, chapter t3 ,noveltype t4\r\n"
					+ "WHERE t1.writerId=t2.writerId \r\n"
					+ "and t1.novelId=t3.novelId \r\n"
					+ "AND t1.novelTypeId1=t4.novelTypeId and t1.audit=1 and t3.audit=1\r\n"
					+ "ORDER BY updateTime DESC LIMIT 0,20;";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<Chapter> NovelList=new ArrayList<Chapter>();
			while(rs.next()) {
				Chapter novel=new Chapter();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
//				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				novel.setChapterId(rs.getInt("chapterId"));
				novel.setChapterName(rs.getString("chapterName"));
				novel.setUpdateTime(rs.getDate("updateTime"));

				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<Chapter> selectshuChapter(String novelId) {//shu.jsp
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			
			String sql = "SELECT t3.chapterId ,t3.chapterName,t3.updateTime,t3.chapterContent\r\n"
					+ "FROM novel t1,writer t2, chapter t3 ,noveltype t4\r\n"
					+ "WHERE t1.writerId=t2.writerId \r\n"
					+ "and t1.novelId=t3.novelId \r\n"
					+ "AND t1.novelTypeId1=t4.novelTypeId and t1.audit=1 and t3.audit=1\r\n"
					+ "AND t1.novelId='"+novelId+"'\r\n"
					+ "ORDER BY updateTime ";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<Chapter> NovelList=new ArrayList<Chapter>();
			while(rs.next()) {
				
				Chapter novel=new Chapter();
				
				novel.setChapterId(rs.getInt("chapterId"));
				novel.setChapterName(rs.getString("chapterName"));
				novel.setUpdateTime(rs.getDate("updateTime"));
				novel.setChapterContent(rs.getString("chapterContent"));
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<NovelWriterType> selectnovelById(String novelId) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "SELECT t1.novelId,t1.novelName,t2.writerId,t2.writerName,t1.novelImgURL ,t4.novelTypeName As novelTypeName2,t1.novelIntroduction ,t3.novelTypeId,t3.novelTypeName\r\n"
					+ "FROM novel t1,writer t2,noveltype t3,novelType t4\r\n"
					+ "WHERE t1.writerId=t2.writerId and t1.novelTypeId2=t4.novelTypeId \r\n"
					+ "AND t1.novelTypeId1=t3.novelTypeId and t1.audit=1\r\n"
					+ "and t1.novelId='"+novelId+"'";
			System.err.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<NovelWriterType> NovelList=new ArrayList<NovelWriterType>();
			while(rs.next()) {
				NovelWriterType novel=new NovelWriterType();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				novel.setNovelTypeName2(rs.getString("novelTypeName2"));
//				novel.setHits(rs.getInt("hits"));
				String str=rs.getString("novelIntroduction");
//				String subStr = str.substring(0, 30);
				novel.setNovelIntroduction(str);
				NovelList.add(novel);
			}
			return NovelList;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	

	@Override
	public int selectChapterNum(String string) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		int num=0;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql = "select count(1) AS chapterNum from chapter where novelId='"+string+"'and audit=1 ORDER BY updateTime DESC";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			
			while(rs.next()) {
				num=rs.getInt("chapterNum");
			}
			return num;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return num;
	}

	@Override
	public Chapter selectNowChapter(String novelId) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
//			if(conn!=null) {
//				System.out.println("连接成功");
//			}
			String sql = "SELECT t1.novelId,t1.novelName,t2.writerId,t2.writerName,t3.chapterId,t3.chapterName,t3.updateTime ,t4.novelTypeId,t4.novelTypeName,t1.novelImgURL ,t3.chapterContent\r\n"
					+ "FROM novel t1,writer t2, chapter t3 ,noveltype t4\r\n"
					+ "WHERE t1.writerId=t2.writerId \r\n"
					+ "and t1.novelId=t3.novelId \r\n"
					+ "AND t1.novelTypeId1=t4.novelTypeId and t1.audit=1 and t3.audit=1\r\n"
					+ "AND t1.novelId='"+novelId+"'\r\n"
					+ "ORDER BY updateTime DESC LIMIT 0,1;";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
//			List<Chapter> NovelList=new ArrayList<Chapter>();
			Chapter novel=new Chapter();
			while(rs.next()) {
				
				
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelTypeId(rs.getInt("novelTypeId"));
				novel.setWriterId(rs.getInt("writerId"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelTypeName(rs.getString("novelTypeName"));
				novel.setChapterId(rs.getInt("chapterId"));
				novel.setChapterName(rs.getString("chapterName"));
				novel.setUpdateTime(rs.getDate("updateTime"));
//				novel.setChapterContent(rs.getString("chapterContent"));
//				NovelList.add(novel);
			}
			return novel;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	}

	@Override
	public List<Novel> selectNovelLimit(int m, int n) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql="SELECT * FROM novel t1,noveltype t2,writer t3 \r\n"
					+ "WHERE t1.novelTypeId1=t2.novelTypeId AND t3.writerId=t1.writerId limit "+m+","+n+"";
			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<Novel> list=new ArrayList<Novel>();
			while(rs.next()) {
				Novel novel =new Novel();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelType1(rs.getString("novelTypeName"));
				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelIntroduction(rs.getString("novelIntroduction"));
				novel.setHits(rs.getInt("hits"));
				novel.setReleaseTime(rs.getDate("releaseTime"));
				list.add(novel);
			}
			return list;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	
	}

	@Override
	public int getInforAllNum() {
		 String sql = "SELECT COUNT(*) as num FROM novel"; // 替换为你的查询
		  Connection conn=BaseDao.getConn();
		  int num=0;
			PreparedStatement s = null;
			ResultSet rs = null;
			try {
				s = conn.prepareStatement(sql);
				rs =  s.executeQuery();
				if (rs.next()) {
		            num = rs.getInt("num");
		            return num;
		        }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {			
				 BaseDao.closeAll(conn, s,rs);
				}
		return num;
	}

	@Override
	public int selectByTypeName(String novelType1) {
		// TODO Auto-generated method stub
		 String sql = "SELECT novelTypeId FROM noveltype where novelTypeName='"+novelType1+"'"; // 替换为你的查询
		 
		  Connection conn=BaseDao.getConn();
		  int num=0;
			PreparedStatement s = null;
			ResultSet rs = null;
			try {
				s = conn.prepareStatement(sql);
				rs =  s.executeQuery();
				if (rs.next()) {
		            num = rs.getInt("novelTypeId");
		            
		            return num;
		        }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {			
				 BaseDao.closeAll(conn, s,rs);
				}
		return num;

	}

	@Override
	public void addNovelType(String novelType1) {
		// TODO Auto-generated method stub
		 String sql = "insert into noveltype(novelTypeName,createTime) values(?,?)"; // 替换为你的查询
		 Date currentDate = new Date(); // 获取当前日期和时间
	     SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
	     String strDate = dateFormat.format(currentDate);
	     
	     Object[] obj= {novelType1,strDate};
	     int num=BaseDao.executeUpdate(sql,obj);
	     System.out.println(num);
		  
	}

	@Override
	public int selectByWriterName(String writerName) {
		// TODO Auto-generated method stub
		 String sql = "SELECT writerId FROM writer where writerName='"+writerName+"'"; // 替换为你的查询
		 System.out.println(sql);
		  Connection conn=BaseDao.getConn();
		  int num=0;
			PreparedStatement s = null;
			ResultSet rs = null;
			try {
				s = conn.prepareStatement(sql);
				rs =  s.executeQuery();
				if (rs.next()) {
		            num = rs.getInt("writerId");
		            return num;
		        }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {			
				 BaseDao.closeAll(conn, s,rs);
				}
		return num;

	}

	@Override
	public void addWriter(String writerName) {
		// TODO Auto-generated method stub
		 String sql = "insert into writer(writerName) values(?)"; // 替换为你的查询
		
	     Object[] obj= {writerName};
	     int num=BaseDao.executeUpdate(sql,obj);
	     System.out.println(num);
	}

	@Override
	public int updateNovel(Novel novel, int novelTypeId, int writerId) {
		// TODO Auto-generated method stub
		String sql="update novel set novelName=?,novelImgURL=?,novelTypeId1=?,novelIntroduction=?,hits=?,writerId=? where novelId=?";
		 Object[] obj= {novel.getNovelName(),novel.getNovelImgURL(),novelTypeId,novel.getNovelIntroduction(),novel.getHits(),writerId,novel.getNovelId()};
		 int num=BaseDao.executeUpdate(sql,obj);
		 return num;
	}

	@Override
	public String selectChapterByNovelId(int novelId) {
		// TODO Auto-generated method stub
		 String sql = "SELECT chapterId FROM chapter where novelId='"+novelId+"'"; // 替换为你的查询
		 System.out.println(sql);
		  Connection conn=BaseDao.getConn();
		  String in="";
		  
			PreparedStatement s = null;
			ResultSet rs = null;
			try {
				s = conn.prepareStatement(sql);
				rs =  s.executeQuery();
				while (rs.next()) {
					int num=rs.getInt("chapterId");
					System.out.println(num);
		            in +=num+",";
		           
		        }
				System.out.println(in+"这是章节id");
				 return in;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {			
				 BaseDao.closeAll(conn, s,rs);
				}
		return in;
	}

	@Override
	public int deleteChapterByIds(int chapterIds) {
		// TODO Auto-generated method stub
		String sql = "delete from chapter where novelId="+chapterIds+""; // 删除章节
		String sql2 = "delete from comment where novelId="+chapterIds+""; // 删除有关小说的评论
//		String sql2 = "delete from writer where novelId ="+chapterIds+"";
		System.out.println(sql);
	    int num=BaseDao.executeUpdate(sql);
//	    int num2=BaseDao.executeUpdate(sql2);
		return num;
	}

	@Override
	public int deleteNovelByNovelId(int novelId) {
		// TODO Auto-generated method stub
		String sql = "delete from novel where novelId='"+novelId+"'"; // 替换为你的查询
		System.out.println(sql);
	    int num=BaseDao.executeUpdate(sql);
		return num;
	}

	@Override
	public int addNovel(Novel novel, int novelTypeId, int writerId) {
		// TODO Auto-generated method stub
		String sql="insert into novel(novelName,novelImgURL,novelTypeId1,releaseTime,novelIntroduction,hits,writerId,audit) value(?,?,?,?,?,?,?,1)";
		 Object[] obj= {novel.getNovelName(),novel.getNovelImgURL(),novelTypeId,novel.getReleaseTime(),novel.getNovelIntroduction(),novel.getHits(),writerId};
		 int num=BaseDao.executeUpdate(sql,obj);
		 return num;
	}

	public static int getSousuoInforAllNum(String sousuo) {
		// TODO Auto-generated method stub
		String c="%"+sousuo+"%";
		String sql = "SELECT count(1) AS num FROM\r\n"
				+ "(SELECT t1.novelName,t1.novelImgURL,t1.novelId,t2.writerName,t1.releaseTime,t1.novelIntroduction,t1.hits,t3.novelTypeName\r\n"
				+ "FROM novel t1,writer t2 ,novelType t3 WHERE t1.writerId=t2.writerId AND t3.novelTypeId=t1.novelTypeId1 ) AS t9\r\n"
				+ "WHERE 1=1 AND novelName LIKE '"+c+"' OR writerName LIKE '"+c+"' OR noveltypeName LIKE '"+c+"'";
		  Connection conn=BaseDao.getConn();
		  int num=0;
			PreparedStatement s = null;
			ResultSet rs = null;
			try {
				s = conn.prepareStatement(sql);
				rs =  s.executeQuery();
				if (rs.next()) {
		            num = rs.getInt("num");
		            return num;
		        }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {			
				 BaseDao.closeAll(conn, s,rs);
				}
		return num;
	}

	public static List<Novel> sousuoSelectUserLimit(int i, int aPageNum, String sousuo) {
		// TODO Auto-generated method stub
		String c="%"+sousuo+"%";
		String sql = "SELECT * FROM\r\n"
				+ "(SELECT t1.novelName,t1.novelImgURL,t1.novelId,t2.writerName,t1.releaseTime,t1.novelIntroduction,t1.hits,t3.novelTypeName\r\n"
				+ "FROM novel t1,writer t2 ,novelType t3 WHERE t1.writerId=t2.writerId AND t3.novelTypeId=t1.novelTypeId1 ) AS t9\r\n"
				+ "WHERE 1=1 AND novelName LIKE '"+c+"' OR writerName LIKE '"+c+"' OR noveltypeName LIKE '"+c+"'";
		  Connection conn=BaseDao.getConn();
		  System.out.println(sql);
			PreparedStatement s = null;
			ResultSet rs = null;
			try {
				s = conn.prepareStatement(sql);
				rs =  s.executeQuery();
				List<Novel> list=new ArrayList<Novel>();
				while(rs.next()) {
					Novel novel =new Novel();
					novel.setNovelId(rs.getInt("novelId"));
					novel.setNovelName(rs.getString("novelName"));
					novel.setNovelImgURL(rs.getString("novelImgURL"));
					novel.setNovelType1(rs.getString("novelTypeName"));
					novel.setWriterName(rs.getString("writerName"));
					novel.setNovelIntroduction(rs.getString("novelIntroduction"));
					novel.setHits(rs.getInt("hits"));
					novel.setReleaseTime(rs.getDate("releaseTime"));
					list.add(novel);
				}
				return list;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {			
				 BaseDao.closeAll(conn, s,rs);
				}
		return null;
	}

	@Override
	public List<Novel> SelectCollectionBook(User user) {
		// TODO Auto-generated method stub
			Connection conn=BaseDao.getConn();//打开连接
			String sql = "SELECT * FROM novel WHERE novelId IN(SELECT novelId from collection WHERE userId = '"+user.getUserId()+"')";
			List<Novel> list = new ArrayList<Novel>();
			PreparedStatement ps= null;
			ResultSet rs=null;
			try {
				ps= conn.prepareStatement(sql);
				rs = ps.executeQuery();			
				while(rs.next()) {
					Novel novel = new Novel();
					novel.setNovelId(rs.getInt("novelId"));
					novel.setNovelImgURL(rs.getString("novelImgURL"));
					novel.setNovelIntroduction(rs.getString("novelIntroduction"));
					novel.setNovelName(rs.getString("novelName"));
					novel.setNovelType1(rs.getString("novelTypeId1"));
					novel.setReleaseTime(rs.getDate("releaseTime"));
					novel.setWriterName(rs.getString("writerId"));
					list.add(novel);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				BaseDao.closeAll(conn, ps,rs);
			}
			return list;
	}
		//liheng
	@Override
	public List<Novel> SelectRecentBook(User user) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT * FROM novel WHERE novelId IN(SELECT novelId from readHistory WHERE userId = '"+user.getUserId()+"') limit 0,6";
		List<Novel> list = new ArrayList<Novel>();
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			while(rs.next()) {
				Novel novel = new Novel();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelIntroduction(rs.getString("novelIntroduction"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelType1(rs.getString("novelTypeId1"));
				novel.setReleaseTime(rs.getDate("releaseTime"));
				novel.setWriterName(rs.getString("writerId"));
				list.add(novel);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}

	@Override
	public int DeleteCollectionBook(Integer userId, Integer novelId) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM collection WHERE novelId = '"+novelId+"' AND userId = '"+userId+"'";
//		System.out.println(sql);
		int num = BaseDao.executeUpdate(sql);
		return num;
	}
/////

	@Override
	public int addRead(int userId, String novelId) {
		// TODO Auto-generated method stub
		LocalDate currentDate = LocalDate.now();
		String sql = "insert into readHistory(userId,novelId,readTime) values(?,?,?)";
		Object[] obj= {userId,Integer.parseInt(novelId),currentDate};
		System.out.println(sql);
		int num = BaseDao.executeUpdate(sql,obj);
		return num;
	}

	@Override
	public List<Novel> selectVIP() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT * FROM novel t1,vipbook t2 where t1.novelId=t2.novelId";
		List<Novel> list = new ArrayList<Novel>();
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			while(rs.next()) {
				Novel novel = new Novel();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
//				novel.setNovelIntroduction(rs.getString("novelIntroduction"));
				novel.setNovelName(rs.getString("novelName"));
//				novel.setNovelType1(rs.getString("novelTypeId1"));
//				novel.setReleaseTime(rs.getDate("releaseTime"));
				novel.setWriterName(rs.getString("writerId"));
				list.add(novel);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;
	}

	@Override
	public List<LBVipBook> selectVIPBook() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "SELECT t1.novelId,t1.novelName,t2.vipBookId FROM novel t1,vipbook t2 where t1.novelId=t2.novelId";
		List<LBVipBook> list = new ArrayList<LBVipBook>();
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			while(rs.next()) {
				LBVipBook novel = new LBVipBook();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setVipBookId(rs.getInt("vipBookId"));
				list.add(novel);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return list;

	}

	@Override
	public int updateVIPBook(String novelId, String vipBookId) {
		// TODO Auto-generated method stub
		String sql = "update vipbook set novelId='"+novelId+"' where vipBookId='"+vipBookId+"'";
		
		System.out.println(sql);
		int num = BaseDao.executeUpdate(sql);
		return num;
	}

	@Override
	public int addHits(String novelId) {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();//打开连接
		String sql = "select hits from novel where novelId="+novelId+"";
		int hits=0;
		PreparedStatement ps= null;
		ResultSet rs=null;
		try {
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();			
			while(rs.next()) {
				hits=rs.getInt("hits");
			}
			hits=hits+1;
			String sql2 = "update novel set hits="+hits+" where novelId="+novelId+"";
			BaseDao.executeUpdate(sql2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(conn, ps,rs);
		}
		return 0;

	}

	@Override
	public List<Novel> selectNovelaudit() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			if(conn!=null) {
				System.out.println("连接成功");
			}
			String sql="SELECT t1.*,t2.novelTypeName,t3.novelTypeName AS ntn FROM novel t1,noveltype t2,noveltype t3\r\n"
					+ "WHERE t1.novelTypeId1=t2.novelTypeId\r\n"
					+ "AND t1.novelTypeId2=t3.novelTypeId\r\n"
					+ "AND audit=0;";
//			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<Novel> list=new ArrayList<Novel>();
			while(rs.next()) {
				Novel novel =new Novel();
				novel.setNovelId(rs.getInt("novelId"));
				novel.setNovelName(rs.getString("novelName"));
				novel.setNovelImgURL(rs.getString("novelImgURL"));
				novel.setNovelType1(rs.getString("novelTypeName"));
				novel.setNovelType2(rs.getString("ntn"));
//				novel.setWriterName(rs.getString("writerName"));
				novel.setNovelIntroduction(rs.getString("novelIntroduction"));
				list.add(novel);
			}
			return list;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	
	}

	@Override
	public List<Chapter> selectChapteraudit() {
		// TODO Auto-generated method stub
		Connection conn=BaseDao.getConn();
		PreparedStatement s = null;
		ResultSet rs = null;
		try {
			
			String sql="select * from chapter where audit=0";
//			System.out.println(sql);
			s = conn.prepareStatement(sql);
			rs =  s.executeQuery();
			List<Chapter> list=new ArrayList<Chapter>();
			while(rs.next()) {
				Chapter c =new Chapter();
				c.setChapterId(rs.getInt("chapterId"));
				c.setChapterContent(rs.getString("chapterContent"));
				c.setChapterName(rs.getString("chapterName"));
				list.add(c);
			}
			return list;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {			
			BaseDao.closeAll(conn, s,rs);
		}
		return null;
	
	}

	@Override
	public int deleteReadHistory(int novelId) {
		// TODO Auto-generated method stub
		String sql="delete from readHistory where novelId="+novelId+"";
		int num=BaseDao.executeUpdate(sql);
		return num;
	}

	
}
